/*
 * public class DataService
 * author: adam barney
 * contains a bucket of protected static query methods.
 */

/*
 * This file is part of WhatItDo.
 *
 * WhatItDo is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * WhatItDo is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with WhatItDo.  If not, see <http://www.gnu.org/licenses/>.
 */

package net.sourceforge.whatitdo.billboard.model;

import java.sql.*;
import java.util.ArrayList;

public class DataService {

    protected Billboard queryBillboard(String idString) throws SQLException {
	// Connection conn = new Connection();
	Connection conn = getConnection();
	/*
	 * Create a new Billboard instance, populate it with data from the DB,
	 * and return it.
	 */
	Billboard rtrnBillboard = new Billboard();
	rtrnBillboard.setId(idString);

	Statement statement = conn.createStatement();

	String query = ("SELECT name FROM whatitdo_v_0_2.billboard WHERE billboard_id = '"
		+ idString + "'");
	rtrnBillboard.setName(query);

	// Drop the connection when we're done

	return rtrnBillboard;
    }

    protected static String getName(String idString) {
	String rtrn = new String();
	Connection conn = getConnection();
	Statement statement;
	try {
	    statement = conn.createStatement();
	    String query = ("SELECT name FROM whatitdo_v_0_2.billboard WHERE billboard_id = '"
		    + idString + "'");
	    ResultSet nameResults = statement.executeQuery(query);
	    nameResults.next();
	    rtrn = nameResults.getString("name");
	    conn.close();
	} catch (SQLException e) {
	    // TODO Auto-generated catch block
	    e.printStackTrace();
	}
	return rtrn;

    }

    protected static String getTickerText(String idString) {
	String rtrn = new String();
	Connection conn = getConnection();
	Statement statement;
	try {
	    statement = conn.createStatement();
	    // String query = ("SELECT text FROM whatitdo.ticker_text WHERE
	    // billboard_id = '" + idString + "'");

	    String query = ("SELECT text FROM whatitdo_v_0_2.ticker_text WHERE billboard_id = '"
		    + idString + "'");
	    ResultSet tickerResults = statement.executeQuery(query);
	    tickerResults.next();
	    rtrn = tickerResults.getString("text");
	    conn.close();
	} catch (SQLException e) {
	    e.printStackTrace();
	}
	return rtrn;
    }

    protected static String getBillboardIdFromName(String name) {
	String rtrn = new String();
	Connection conn = getConnection();
	Statement statement;
	try {
	    statement = conn.createStatement();
	    String query = ("SELECT billboard_id FROM whatitdo_v_0_2.billboard WHERE name = '"
		    + name + "'");
	    ResultSet idResults = statement.executeQuery(query);
	    idResults.next();
	    rtrn = idResults.getString("billboard_id");
	    conn.close();
	} catch (SQLException e) {
	    e.printStackTrace();
	}
	return rtrn;
    }

    /*
     * This method replaces the soon-to-be deprecated getAllNames. We must not
     * only key off of billboard ids rather than billboard names, but we must
     * ensure that only a the owner of a billboard may access it.
     */
    protected static ArrayList<String> getBillboardsForUser(String username) {
	ArrayList<String> rtrn = new ArrayList<String>();
	Connection conn = getConnection();
	Statement statement;

	try {
	    statement = conn.createStatement();
	    String query = ("SELECT billboard_id FROM whatitdo_v_0_2.billboard WHERE username = '"
		    + username + "'");
	    ResultSet results = statement.executeQuery(query);
	    while (results.next()) {
		rtrn.add(results.getString("billboard_id"));
	    }
	    conn.close();
	} catch (SQLException e) {
	    e.printStackTrace();
	}
	return rtrn;
    }

    /*
     * This method replaces the soon-to-be deprecated getAllNames. We must not
     * only key off of billboard ids rather than billboard names, but we must
     * ensure that only a the owner of a billboard may access it.
     */
    protected static ArrayList<String> getBillboardNamesForUser(String username) {
	ArrayList<String> rtrn = new ArrayList<String>();
	Connection conn = getConnection();
	Statement statement;

	try {
	    statement = conn.createStatement();
	    String query = ("SELECT name FROM whatitdo_v_0_2.billboard WHERE username = '"
		    + username + "'");
	    ResultSet results = statement.executeQuery(query);
	    while (results.next()) {
		rtrn.add(results.getString("name"));
	    }
	    conn.close();
	} catch (SQLException e) {
	    e.printStackTrace();
	}
	return rtrn;
    }

    protected static ArrayList<String> getSlideshowText(String idString) {
	ArrayList<String> rtrn = new ArrayList<String>();
	Connection conn = getConnection();
	Statement statement;

	try {
	    statement = conn.createStatement();
	    String query = ("SELECT text FROM whatitdo_v_0_2.slideshow_text WHERE billboard_id = '"
		    + idString + "' ORDER BY order_number");
	    ResultSet results = statement.executeQuery(query);
	    while (results.next()) {
		rtrn.add(results.getString("text"));
	    }
	    conn.close();
	} catch (SQLException e) {
	    e.printStackTrace();
	}
	return rtrn;
    }

    /*
     * Returns an ArrayList of all billboard names in the database. This method
     * was included for the April demo, and has no place in release 1. This
     * method will be deprecated as soon as the user login feature is working.
     * DO NOT USE THIS METHOD.
     */
    // protected static ArrayList<String> getAllNames() {
    // ArrayList<String> rtrn = new ArrayList<String>();
    // Connection conn = getConnection();
    // Statement statement;
    //
    // try {
    // statement = conn.createStatement();
    // String query = ("SELECT name FROM whatitdo_v_0_2.billboard");
    // ResultSet nameResults = statement.executeQuery(query);
    // while (nameResults.next()) {
    // System.out.println("First name: "
    // + nameResults.getString("name"));
    // rtrn.add(nameResults.getString("name"));
    //
    // }
    // conn.close();
    // } catch (SQLException e) {
    // e.printStackTrace();
    // }
    //
    // return rtrn;
    //
    // }
    protected static boolean isUniqueBillboardId(String idString) {
	try {
	    Connection conn = getConnection();
	    Statement statement = conn.createStatement();
	    String query = ("SELECT billboard_id FROM whatitdo_v_0_2.billboard");
	    ResultSet results = statement.executeQuery(query);
	    while (results.next()) {
		if (results.getString("billboard_id").equals(idString)) {
		    conn.close();
		    return false;
		}
	    }
	    conn.close();
	} catch (SQLException e) {

	    e.printStackTrace();
	    return false;
	}
	return true;
    }

    protected static boolean isPublished(String idString) {
	try {
	    Connection conn = getConnection();
	    Statement statement = conn.createStatement();
	    String query = ("SELECT published FROM whatitdo_v_0_2.billboard WHERE billboard_id = "
		    + "'" + idString + "'");
	    ResultSet results = statement.executeQuery(query);
	    results.next();
	    if (results.getInt("published") == 1) {
		System.out.println("This billboard is published");
		conn.close();
		return true;
	    } else {
		System.out.println("This billboard is not published");
		conn.close();
		return false;
	    }
	} catch (SQLException e) {
	    e.printStackTrace();
	    return false;
	}
    }

    protected static boolean createNewBillboard(String newId, String username) {
	try {
	    Connection conn = getConnection();
	    Statement statement = conn.createStatement();
	    String insertQuery = ("INSERT INTO whatitdo_v_0_2.billboard (billboard_id, name, username) VALUES('"
		    + newId + "','" + newId + "','" + username + "')");
	    statement.execute(insertQuery);
	} catch (SQLException e) {
	    e.printStackTrace();
	    return false;
	}
	return true;

    }

    public static void deleteBillboard(String id) {
	try {
	    Connection conn = getConnection();
	    Statement statement = conn.createStatement();
	    String query = ("DELETE FROM whatitdo_v_0_2.billboard WHERE billboard_id = '" + id + "'");
	    statement.execute(query);
	} catch (SQLException e) {
	    e.printStackTrace();
	}
    }

    protected static boolean writeBillboard(Billboard billboard) {
	try {
	    Connection conn = getConnection();
	    Statement statement = conn.createStatement();
	    /*
	     * Save billboard tickerText
	     */
	    String selectQuery = ("SELECT text FROM whatitdo_v_0_2.ticker_text WHERE billboard_id = '"
		    + billboard.getId() + "'");
	    ResultSet results = statement.executeQuery(selectQuery);
	    if (results.first()) {
		String updateQuery = ("UPDATE whatitdo_v_0_2.ticker_text SET text = '"
			+ billboard.getTickerText()
			+ "' WHERE billboard_id = '" + billboard.getId() + "'");
		statement.execute(updateQuery);
	    } else {
		String insertQuery = ("INSERT INTO whatitdo_v_0_2.ticker_text (billboard_id, text) VALUES('"
			+ billboard.getId() + "','" + billboard.getTickerText() + "')");
		statement.execute(insertQuery);
	    }

	    /*
	     * Set isPublished only if true
	     */
	    if (billboard.isPublished()) {
		System.out.println("PUBLISHED!");
	    } else {
		System.out.println("NOT PUBLISHED!!!!");
	    }
	    if (billboard.isPublished()) {
		// first, set all other billboards to be unpublished
		String updateQuery = ("UPDATE whatitdo_v_0_2.billboard SET published = 0");
		statement.execute(updateQuery);
		updateQuery = ("UPDATE whatitdo_v_0_2.billboard SET published = 1 WHERE billboard_id = '"
			+ billboard.getId() + "'");
		statement.execute(updateQuery);
	    }

	    /*
	     * Save billboard name
	     */

	    selectQuery = ("SELECT name FROM whatitdo_v_0_2.billboard WHERE billboard_id = '"
		    + billboard.getId() + "'");
	    results = statement.executeQuery(selectQuery);
	    if (results.first()) {
		String updateQuery = ("UPDATE whatitdo_v_0_2.billboard SET name = '"
			+ billboard.getName()
			+ "' WHERE billboard_id = '"
			+ billboard.getId() + "'");
		statement.execute(updateQuery);
	    } else {
		System.out
			.println("This println needs to be replaced with some code that can actually create a new record in the billboard table.");
	    }
	    /*
	     * First, delete all slideshow_text records for this billboard_id,
	     * then insert totally new records, manufacturing their order number
	     * indexed beginning at 1, not 0.
	     */
	    String deleteQuery = ("DELETE FROM whatitdo_v_0_2.slideshow_text WHERE billboard_id = '"
		    + billboard.getId() + "'");
	    System.out.println("Deleting all slideshow texts");
	    statement.execute(deleteQuery);
	    for (int i = 0; i < billboard.getSlideshowText().size(); i++) {
		int slideOrder = i + 1;
		String updateQuery = ("INSERT INTO whatitdo_v_0_2.slideshow_text (billboard_id, text, order_number) VALUES('"
			+ billboard.getId()
			+ "','"
			+ billboard.getSlideshowText().get(i)
			+ "','"
			+ slideOrder + "')");
		System.out.println("inserting slideshow_text: "
			+ billboard.getSlideshowText().get(i));
		statement.execute(updateQuery);
	    }
	    conn.close();
	    return true;
	} catch (SQLException e) {
	    e.printStackTrace();
	    return false;
	}

    }

    /*
     * This query method takes a username as a parameter, and returns the
     * password.
     */
    protected static String getPasswordFromUsername(String userName) {
	String rtrn = new String();
	try {
	    Connection conn = getConnection();
	    Statement statement = conn.createStatement();
	    String query = ("SELECT password FROM whatitdo_v_0_2.user WHERE username = '"
		    + userName + "'");
	    ResultSet results = statement.executeQuery(query);
	    results.next();
	    rtrn = results.getString("password");
	    return rtrn;
	} catch (SQLException e) {
	    e.printStackTrace();
	}
	/*
	 * TODO: figure out what to return if exception is thrown.
	 */
	return "lksenfn";
    }

    protected static String getPublishedBillboardId() {
	String rtrn = new String();
	try {
	    Connection conn = getConnection();
	    Statement statement = conn.createStatement();
	    String query = ("SELECT billboard_id FROM whatitdo_v_0_2.billboard WHERE published = 1");
	    ResultSet results = statement.executeQuery(query);
	    results.next();
	    rtrn = results.getString("billboard_id");
	} catch (SQLException e) {
	    e.printStackTrace();
	}
	return rtrn;
    }

    private static Connection getConnection() {
	Connection conn = null;
	try {
	    String userName = "testuser";
	    String password = "testpass";
	    String url = "jdbc:mysql://localhost/whatitdo_v_0_2";
	    Class.forName("com.mysql.jdbc.Driver").newInstance();
	    conn = DriverManager.getConnection(url, userName, password);
	    System.out.println("Database connection established");
	} catch (Exception e) {
	    e.printStackTrace();
	    System.err.println("Cannot connect to database server");
	    System.out.println("Cannot connect to database.");
	}
	return conn;
    }

    /*
     * This method was a misguided attempt at closing the connection through a
     * static method. This doesn't work too well.
     */
    // private static void releaseConnection(Connection conn) {
    // if (conn != null) {
    // try {
    // conn.close();
    // System.out.println("Database connection terminated");
    // } catch (Exception e) {
    // e.printStackTrace();
    // }
    // }
    // }
}
